package utils

import (
	"encoding/json"
	"errors"
	"fmt"
	"github.com/tealeg/xlsx/v3"
	"path/filepath"
	"reflect"
	"strconv"
)

const (
	END  = "END"
	TRUE = "1"
	YES  = "YES"
)

const (
	ParamSheet    = "参数表"
	ParamOldSheet = "2" // (旧工具兼容)
	MainKSheet    = "主建表"
	OldKeySheet   = "1" // (旧工具兼容)
	ArraySheet    = "数组表"
	GroupSheet    = "分组表"
)

const (
	ValueRaw       = "raw"
	ValueInt       = "int"   // (旧工具兼容)
	ValueLong      = "long"  // (旧工具兼容)
	ValueFloat     = "float" // (旧工具兼容)
	ValueNumber    = "number"
	ValueString    = "string"
	ValueNewArray  = "[]"
	ValueOldArray  = "array"  // (旧工具兼容)
	ValueOldObject = "object" // (旧工具兼容)
	ValueLanguage  = "language"
)

var MainMetaFiles = []string{"file", "name", "client", "server", "type"}
var ParamMetaFiles = []string{"客户端", "服务端", "key", "数据类型", "value"}

type VolumeMeta struct {
	cellIdx      int    // 列索引
	keyType      string // 字段类型
	exportClient bool   // 是否导出前端配置
	exportServer bool   // 是否导出后端配置
}

// ExcelSheetMeta 表格元数据定义
type ExcelSheetMeta struct {
	sheetName      string                   // sheet名
	exportClient   bool                     // 是否导出前端配置
	exportServer   bool                     // 是否导出后端配置
	exportFileName string                   // 导出文件名
	sheetType      string                   // 表格类型
	mainKey        string                   // 主键名
	volumeMap      map[string]VolumeMeta    // 表格数据行数
	volumeAllData  interface{}              // 表格对象数据
	volumeParamMap []map[string]interface{} // 参数表数据
	readFinish     bool                     // 是否读取完成
}

// ExcelFile 表格文件
type ExcelFile struct {
	file       *xlsx.File            // 打开的表格
	path       string                // 表格
	volumeMap  map[string]VolumeMeta // 字段对应的数据定义
	sheetMetas []*ExcelSheetMeta     // sheet元数据
}

func newExcelSheetMeta() *ExcelSheetMeta {
	return &ExcelSheetMeta{
		sheetName:      "",
		exportClient:   false,
		exportServer:   false,
		exportFileName: "",
		sheetType:      "",
		mainKey:        "",
		volumeMap:      make(map[string]VolumeMeta),
		volumeAllData:  nil,
		volumeParamMap: nil,
		readFinish:     false,
	}
}

func readSheetVolume(sheet *xlsx.Sheet, volumeMap map[string]VolumeMeta, isMain bool) error {
	// 获取对应字段对应列
	row, err := sheet.Row(0)
	if err != nil {
		return err
	}
	var rowVolumeType, rowExportClient, rowExportServer *xlsx.Row
	if !isMain {
		for rowIdx := 1; rowIdx < sheet.MaxRow; rowIdx++ {
			searchRow, err := sheet.Row(rowIdx)
			if err != nil {
				continue
			}
			switch searchRow.GetCell(0).String() {
			case "数据类型":
				rowVolumeType = searchRow
				break
			case "客户端":
				rowExportClient = searchRow
				break
			case "服务端":
				rowExportServer = searchRow
				break
			}
		}
	}
	for cellIdx := 1; cellIdx < sheet.MaxCol; cellIdx++ {
		cell := row.GetCell(cellIdx)
		if cell.Value == END {
			break
		}
		volumeM := VolumeMeta{
			cellIdx:      cellIdx,
			keyType:      "",
			exportClient: false,
			exportServer: false,
		}
		if !isMain {
			if rowVolumeType != nil {
				volumeM.keyType = rowVolumeType.GetCell(cellIdx).Value
			}
			if rowExportClient != nil {
				volumeM.exportClient = rowExportClient.GetCell(cellIdx).Value == YES
			}
			if rowExportServer != nil {
				volumeM.exportServer = rowExportServer.GetCell(cellIdx).Value == YES
			}
			if !volumeM.exportClient && !volumeM.exportServer {
				continue
			}
		}
		volumeMap[cell.Value] = volumeM
	}
	return nil
}

func readCellValue(cell *xlsx.Cell, keyType string) (interface{}, error) {
	switch keyType {
	case ValueRaw:
		vi, err := cell.Int64()
		if err == nil {
			return vi, nil
		}
		vf, err := cell.Float()
		if err == nil {
			return vf, nil
		}
		return cell.Value, nil
	case ValueString:
		return cell.String(), nil
	case ValueInt, ValueLong, ValueFloat, ValueNumber:
		v, err := cell.Int64()
		if err != nil {
			return cell.Float()
		}
		return v, nil
	case ValueLanguage:
		return cell.String(), nil
	case ValueNewArray, ValueOldArray:
		str := cell.String()
		if str == "" {
			str = "[]"
		}
		var js []interface{}
		err := json.Unmarshal([]byte(str), &js)
		if err == nil {
			return js, nil
		}
		return nil, errors.New(fmt.Sprintf("内容:%s 解析异常:%s", str, err.Error()))
	case ValueOldObject:
		str := cell.String()
		if str == "" {
			str = "{}"
		}
		var js map[string]interface{}
		err := json.Unmarshal([]byte(str), &js)
		if err == nil {
			return js, nil
		}
		return nil, errors.New(fmt.Sprintf("内容:%s 解析异常:%s", str, err.Error()))
	}
	return nil, errors.New(fmt.Sprintf("无法解析的字段类型:%s", keyType))
}

// 读main表
func (exf *ExcelFile) readExcelMainSheet() error {
	mainSheet := exf.file.Sheet["main"]
	if mainSheet == nil {
		mainSheet = exf.file.Sheets[0]
		if mainSheet == nil {
			return errors.New(fmt.Sprintf("文件:%s main表缺失\n", exf.path))
		}
	}
	// 字段,名称,注释
	if mainSheet.MaxRow < 3 {
		return errors.New("main表缺失主要表头配置")
	}
	err := readSheetVolume(mainSheet, exf.volumeMap, true)
	if err != nil {
		return errors.New(fmt.Sprintf("读取main表异常:%s", err.Error()))
	}
	// 校验是否符合最基础file数量
	for _, k := range MainMetaFiles {
		if _, ok := exf.volumeMap[k]; !ok {
			return errors.New(fmt.Sprintf("main表缺失字段:%s", k))
		}
	}
	// 遍历每一行数据 file, name, client, server, type
	for rowIdx := 0; rowIdx < mainSheet.MaxRow; rowIdx++ {
		row, err := mainSheet.Row(rowIdx)
		if err != nil {
			continue
		}
		fieldValue := row.GetCell(0).Value
		if fieldValue == END {
			break
		}
		if fieldValue != TRUE {
			continue
		}
		sheetMeta := newExcelSheetMeta()
		sheetMeta.sheetName = row.GetCell(exf.volumeMap["name"].cellIdx).Value
		sheetMeta.exportFileName = row.GetCell(exf.volumeMap["file"].cellIdx).Value
		if sheetMeta.exportFileName == "" {
			println(fmt.Sprintf("xxxxxxxxxxxxxxxxxxxxxxxxxxxxx 文件:%s [%s]表缺失导出文件名", exf.path, sheetMeta.sheetName))
			continue
		}
		sheetMeta.exportClient = row.GetCell(exf.volumeMap["client"].cellIdx).Value == "YES"
		sheetMeta.exportServer = row.GetCell(exf.volumeMap["server"].cellIdx).Value == "YES"
		sheetMeta.sheetType = row.GetCell(exf.volumeMap["type"].cellIdx).Value
		exf.sheetMetas = append(exf.sheetMetas, sheetMeta)
	}
	return nil
}

func (exf *ExcelFile) readExcelParamSheet(sheetMeta *ExcelSheetMeta) error {
	readSheet := exf.file.Sheet[sheetMeta.sheetName]
	if readSheet == nil {
		return errors.New(fmt.Sprintf("文件:%s [%s]表缺失\n", exf.path, sheetMeta.sheetName))
	}
	// 读取表头
	topRow, err := readSheet.Row(0)
	if err != nil {
		return errors.New(fmt.Sprintf("文件:%s [%s]表 表头缺失\n", exf.path, sheetMeta.sheetName))
	}
	volumeMap := make(map[string]int)
	for cellIdx := 0; cellIdx < readSheet.MaxCol; cellIdx++ {
		cell := topRow.GetCell(cellIdx)
		volumeMap[cell.Value] = cellIdx
	}
	if len(volumeMap) == 0 {
		println(fmt.Sprintf("xxxxxxxxxxxxxxxxxxxxxxxxxxxxx 文件:%s 表格:%s 无法获取字段", exf.path, sheetMeta.sheetName))
		return nil
	}
	// 校验是否符合最基础file数量
	for _, k := range ParamMetaFiles {
		if _, ok := volumeMap[k]; !ok {
			return errors.New(fmt.Sprintf("文件:%s 表格:%s 参数表表缺失字段:%s", exf.path, sheetMeta.sheetName, k))
		}
	}
	sheetMeta.volumeParamMap = make([]map[string]interface{}, 2)
	// client
	sheetMeta.volumeParamMap[0] = make(map[string]interface{})
	// server
	sheetMeta.volumeParamMap[1] = make(map[string]interface{})
	for rowIdx := 1; rowIdx < readSheet.MaxRow; rowIdx++ {
		row, err := readSheet.Row(rowIdx)
		if err != nil {
			continue
		}
		fieldValue := row.GetCell(0).Value
		if fieldValue == END {
			break
		}
		if fieldValue != TRUE {
			continue
		}
		exportC := row.GetCell(volumeMap["客户端"]).Value == YES
		exportS := row.GetCell(volumeMap["服务端"]).Value == YES
		if !exportC && !exportS {
			continue
		}
		key := row.GetCell(volumeMap["key"]).Value
		value, err := readCellValue(row.GetCell(volumeMap["value"]), row.GetCell(volumeMap["数据类型"]).Value)
		if err != nil {
			return errors.New(fmt.Sprintf("第%d行 第%d列 value读取异常:%s", rowIdx+1, volumeMap["value"]+1, err.Error()))
		}
		if exportC {
			sheetMeta.volumeParamMap[0][key] = value
		}
		if exportS {
			sheetMeta.volumeParamMap[1][key] = value
		}
	}
	sheetMeta.readFinish = true
	return nil
}

func (exf *ExcelFile) readExcelSheet(sheetMeta *ExcelSheetMeta) error {
	if sheetMeta.sheetType == ParamSheet || sheetMeta.sheetType == ParamOldSheet {
		return exf.readExcelParamSheet(sheetMeta)
	}
	readSheet := exf.file.Sheet[sheetMeta.sheetName]
	if readSheet == nil {
		return errors.New(fmt.Sprintf("文件:%s [%s]表缺失\n", exf.path, sheetMeta.sheetName))
	}
	// 符号,数据类型,数据校验,客户端,服务端,名称,注释
	if readSheet.MaxRow < 5 {
		return errors.New(fmt.Sprintf("表格:%s 缺失主要表头配置", sheetMeta.sheetName))
	}
	err := readSheetVolume(readSheet, sheetMeta.volumeMap, false)
	if err != nil {
		return errors.New(fmt.Sprintf("表格:%s 字段异常:%s", sheetMeta.sheetName, err.Error()))
	}
	if len(sheetMeta.volumeMap) == 0 {
		println(fmt.Sprintf("xxxxxxxxxxxxxxxxxxxxxxxxxxxxx 文件:%s 表格:%s 无法获取字段", exf.path, sheetMeta.sheetName))
		return nil
	}
	if _, ok := sheetMeta.volumeMap["id"]; !ok {
		minIdx := 999
		for k, meta := range sheetMeta.volumeMap {
			// 优先id字段
			if meta.cellIdx < minIdx && (meta.keyType == ValueInt || meta.keyType == ValueLong || meta.keyType == ValueNumber || meta.keyType == ValueString) {
				sheetMeta.mainKey = k
				minIdx = meta.cellIdx
			}
		}
	} else {
		sheetMeta.mainKey = "id"
	}
	if sheetMeta.mainKey == "" {
		return errors.New(fmt.Sprintf("表格[%s] 主键缺失", sheetMeta.sheetName))
	}
	switch sheetMeta.sheetType {
	case MainKSheet:
		sheetMeta.volumeAllData = make(map[string]interface{})
		break
	case OldKeySheet:
		sheetMeta.volumeAllData = make([]interface{}, 0)
		break
	case ArraySheet:
		sheetMeta.volumeAllData = make([]interface{}, 0)
		break
	case GroupSheet:
		sheetMeta.volumeAllData = make(map[string]interface{})
		break
	}
	for rowIdx := 1; rowIdx < readSheet.MaxRow; rowIdx++ {
		row, err := readSheet.Row(rowIdx)
		if err != nil {
			continue
		}
		fieldValue := row.GetCell(0).Value
		if fieldValue == END {
			break
		}
		if fieldValue != TRUE {
			continue
		}
		id, err := readCellValue(row.GetCell(sheetMeta.volumeMap[sheetMeta.mainKey].cellIdx), sheetMeta.volumeMap[sheetMeta.mainKey].keyType)
		if err != nil {
			return errors.New(fmt.Sprintf("第%d行 主键读取异常:%s", rowIdx+1, err.Error()))
		}
		// 读取行数据
		rowData := make(map[string]interface{})
		for key, volumeM := range sheetMeta.volumeMap {
			if !volumeM.exportClient && !volumeM.exportServer {
				continue
			}
			value, err := readCellValue(row.GetCell(volumeM.cellIdx), volumeM.keyType)
			if err != nil {
				return errors.New(fmt.Sprintf("第%d行 第%d列 值读取异常:%s", rowIdx+1, volumeM.cellIdx+1, err.Error()))
			}
			rowData[key] = value
		}
		// 处理行数据赋值
		switch sheetMeta.sheetType {
		case MainKSheet:
			primaryKey := ""
			switch reflect.ValueOf(id).Kind() {
			case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
				primaryKey = strconv.FormatInt(id.(int64), 10)
				break
			case reflect.String:
				primaryKey = id.(string)
			default:
				return errors.New(fmt.Sprintf("第%d行 主键类型不受支持", rowIdx+1))
			}
			data := sheetMeta.volumeAllData.(map[string]interface{})
			if _, ok := data[primaryKey]; ok {
				return errors.New(fmt.Sprintf("第%d行 主键:%s 冲突异常", rowIdx+1, primaryKey))
			}
			data[primaryKey] = rowData
			break
		case OldKeySheet:
			sheetMeta.volumeAllData = append(sheetMeta.volumeAllData.([]interface{}), rowData)
			break
		case ArraySheet:
			sheetMeta.volumeAllData = append(sheetMeta.volumeAllData.([]interface{}), rowData)
			break
		case GroupSheet:
			primaryKey := ""
			switch reflect.ValueOf(id).Kind() {
			case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
				primaryKey = strconv.FormatInt(id.(int64), 10)
				break
			case reflect.String:
				primaryKey = id.(string)
			default:
				return errors.New(fmt.Sprintf("第%d行 主键类型不受支持", rowIdx+1))
			}
			data := sheetMeta.volumeAllData.(map[string]interface{})
			if _, ok := data[primaryKey]; !ok {
				data[primaryKey] = make([]interface{}, 0)
			}
			data[primaryKey] = append(data[primaryKey].([]interface{}), rowData)
			break
		}
	}
	return nil
}

func (exf *ExcelFile) readExcelSheetByMeta() error {
	for _, sheet := range exf.sheetMetas {
		if !sheet.exportServer && !sheet.exportClient {
			continue
		}
		err := exf.readExcelSheet(sheet)
		if err != nil {
			return errors.New(fmt.Sprintf("表格:%s 读取异常:%s", sheet.sheetName, err.Error()))
		}
		sheet.readFinish = true
	}
	return nil
}

func (exf *ExcelFile) Read() error {
	file, err := xlsx.OpenFile(exf.path)
	if err != nil {
		return err
	}
	exf.file = file
	err = exf.readExcelMainSheet()
	if err != nil {
		return errors.New(fmt.Sprintf("文件:%s 异常: %s", exf.path, err.Error()))
	}
	err = exf.readExcelSheetByMeta()
	if err != nil {
		return errors.New(fmt.Sprintf("文件:%s 异常: %s", exf.path, err.Error()))
	}
	return nil
}

func (sheet *ExcelSheetMeta) jsonFileName() string {
	return filepath.Join("data", PathToBaseName(sheet.exportFileName)+".json")
}

func (exf *ExcelFile) OutputFolder(serverOutPath string, clientOutPath string) error {
	err := CheckAndInitDirectory(serverOutPath)
	if err != nil {
		return errors.New(fmt.Sprintf("服务端输出路径异常:%s - %s", serverOutPath, err.Error()))
	}
	err = CheckAndInitDirectory(clientOutPath)
	if err != nil {
		return errors.New(fmt.Sprintf("客户端输出路径异常:%s - %s", clientOutPath, err.Error()))
	}
	for _, sheet := range exf.sheetMetas {
		if !sheet.readFinish {
			continue
		}
		cOutput := filepath.Join(clientOutPath, sheet.jsonFileName())
		sOutput := filepath.Join(serverOutPath, sheet.jsonFileName())
		if sheet.volumeParamMap == nil {
			js, err := json.Marshal(sheet.volumeAllData)
			if err != nil {
				return err
			}
			if sheet.exportClient {
				//fmt.Printf("输出客户端文件:%s\n", cOutput)
				err = WriteToFile(cOutput, js)
				if err != nil {
					return err
				}
			}
			if sheet.exportServer {
				//fmt.Printf("输出服务端文件:%s\n", sOutput)
				err = WriteToFile(sOutput, js)
				if err != nil {
					return err
				}
			}
		} else {
			if sheet.exportClient {
				js, err := json.Marshal(sheet.volumeParamMap[0])
				if err != nil {
					return err
				}
				//fmt.Printf("输出客户端文件:%s\n", cOutput)
				err = WriteToFile(cOutput, js)
				if err != nil {
					return err
				}
			}
			if sheet.exportServer {
				js, err := json.Marshal(sheet.volumeParamMap[1])
				if err != nil {
					return err
				}
				//fmt.Printf("输出服务端文件:%s\n", sOutput)
				err = WriteToFile(sOutput, js)
				if err != nil {
					return err
				}
			}
		}
	}
	return nil
}

func NewExcelFile(filePath string) *ExcelFile {
	return &ExcelFile{
		file:      nil,
		path:      filePath,
		volumeMap: make(map[string]VolumeMeta),
	}
}
